Using Subqueries with SELECT, WHERE, and FROM in MySQL

Course: MySQL Training

Topic: Using Subqueries with SELECT, WHERE, and FROM

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Subqueries in MySQL

A subquery in MySQL is a SQL query nested inside another SQL statement. It is also called an inner query, while the query containing it is known as the outer query. Subqueries help retrieve data, calculate values, filter records, and create intermediate result sets that can be used by another query.

MySQL supports subqueries in several contexts, including the SELECT list, WHERE clause, and FROM clause. Each location serves a different purpose:

• SELECT clause: Uses a subquery to calculate or retrieve a value that appears as a column in the output.
• WHERE clause: Uses a subquery to filter records according to a value or set of values returned by another query.
• FROM clause: Uses a subquery to create a derived table that the outer query can process.

For example, a company may want to display each employee's salary alongside the company's average salary, find employees earning more than the average, or calculate average salaries by department before filtering the results. These tasks can be performed using subqueries in different clauses.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand the role of subqueries in MySQL.
• Identify the inner query and outer query.
• Use subqueries inside the SELECT clause.
• Filter records using subqueries in the WHERE clause.
• Create derived tables using subqueries in the FROM clause.
• Use aggregate functions such as AVG(), MAX(), MIN(), SUM(), and COUNT() within subqueries.
• Understand scalar subqueries and multiple-row subqueries.
• Use aliases to reference calculated values and derived tables.
• Combine subqueries with GROUP BY, WHERE, and HAVING.
• Identify common errors and improve query readability and performance.

3. Understanding the Basic Structure of a Subquery

A subquery is usually enclosed in parentheses. It may return a single value, a single column of values, a row, or a table-like result set, depending on its context.

3.1 General Syntax

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM another_table
    WHERE condition
);

In this example, the inner query retrieves a value from another table. The outer query uses that value to determine which records should be returned.

3.2 Inner Query and Outer Query

4. Preparing Sample Tables for Practice

To understand subqueries in SELECT, WHERE, and FROM, we will use two related tables: employees and departments.

4.1 Create the Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

4.2 Insert Department Records

INSERT INTO departments
    (department_id, department_name, location)
VALUES
    (10, 'Finance', 'Mumbai'),
    (20, 'IT', 'Pune'),
    (30, 'HR', 'Delhi'),
    (40, 'Marketing', 'Bengaluru');

4.3 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

4.4 Insert Employee Records

INSERT INTO employees
    (employee_id, employee_name, salary, department_id)
VALUES
    (1, 'Amit', 60000, 10),
    (2, 'Priya', 75000, 20),
    (3, 'Rahul', 45000, 10),
    (4, 'Sneha', 90000, 20),
    (5, 'Neha', 50000, 30),
    (6, 'Vikas', 65000, 30),
    (7, 'Anita', 40000, 40);

The employee records will help demonstrate how subqueries retrieve values, filter rows, and create derived tables.

5. Using Subqueries with the SELECT Clause

A subquery in the SELECT clause can calculate a value or retrieve information to display alongside the columns returned by the outer query. When used as a scalar expression, it must return one column and at most one row.

This approach is useful when you want to display a calculated value, such as the company-wide average salary or total employee count, next to individual employee records.

5.1 Basic Syntax

SELECT
    column_name,
    (SELECT aggregate_function(column_name)
     FROM table_name) AS calculated_column
FROM table_name;

5.2 Display Each Employee's Salary and the Company Average

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary)
     FROM employees) AS average_salary
FROM employees;

Explanation:

1. The outer query retrieves employee names and salaries.
2. The inner query calculates the average salary of all employees.
3. The alias average_salary gives the calculated column a readable name.
4. The average salary appears alongside every employee in the result.

For the sample data, the total salary is 425000 and the average salary is approximately 60714.29. The same average value is displayed for each employee because the subquery calculates the company-wide average.

5.3 Display the Total Number of Employees

SELECT
    employee_name,
    salary,
    (SELECT COUNT(*)
     FROM employees) AS total_employees
FROM employees;

The inner query counts all employee records. The outer query displays that count next to each employee. Since the count is independent of the current employee row, the same total is repeated in every output row.

5.4 Display the Highest Salary Alongside Each Employee

SELECT
    employee_name,
    salary,
    (SELECT MAX(salary)
     FROM employees) AS highest_salary
FROM employees;

The subquery finds the maximum salary in the employees table. The outer query displays that maximum salary alongside each employee's own salary.

5.5 Display the Difference Between Each Salary and the Average

SELECT
    employee_name,
    salary,
    salary - (
        SELECT AVG(salary)
        FROM employees
    ) AS difference_from_average
FROM employees;

This query calculates the difference between each employee's salary and the overall average salary. A positive result means the employee earns more than the average, while a negative result means the employee earns less.

5.6 Use a Subquery to Display Department Information

SELECT
    employee_name,
    department_id,
    (
        SELECT department_name
        FROM departments d
        WHERE d.department_id = e.department_id
    ) AS department_name
FROM employees e;

This is a correlated scalar subquery because the inner query references e.department_id from the outer query. It retrieves the matching department name for each employee. The department ID is a primary key in the sample table, so the inner query returns at most one matching department name.

5.7 Important Rules for SELECT Subqueries

• A scalar subquery must return one column and at most one row.
• If a scalar subquery returns no rows, its value is NULL.
• If a scalar subquery returns multiple rows, MySQL raises an error.
• Use meaningful aliases for calculated output columns.
• A correlated subquery can refer to columns from the outer query.
• For large datasets, check whether a JOIN or another query structure is clearer or more efficient.

6. Using Subqueries with the WHERE Clause

The WHERE clause filters records according to a condition. A subquery inside WHERE allows the filtering condition to depend on information retrieved or calculated by another query.

For example, you can find employees whose salaries exceed the company average, employees working in a particular department, or employees whose department IDs match a set of values returned by another query.

6.1 Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

6.2 Find Employees Earning More Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Explanation: The inner query calculates the average salary. The outer query returns only those employees whose salaries are greater than the calculated average.

This example demonstrates a non-correlated subquery because the inner query does not refer to a column from the outer query.

6.3 Find Employees Earning the Highest Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The inner query returns the highest salary. The outer query retrieves employees whose salaries equal that value. If multiple employees share the highest salary, all matching employees are returned.

6.4 Find Employees Earning Less Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
);

This query selects employees earning less than the overall average salary.

6.5 Find Employees Working in the Finance Department

SELECT employee_name, department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Finance'
);

The inner query retrieves the department ID for Finance. The outer query returns employees assigned to that department. This query assumes that the department name identifies exactly one department.

6.6 Use IN When the Subquery Can Return Multiple Rows

If the inner query may return multiple department IDs, use IN instead of an ordinary scalar equality comparison.

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location IN ('Mumbai', 'Pune')
);

The inner query returns department IDs for departments located in Mumbai or Pune. The outer query returns employees whose department IDs appear in that result set.

6.7 Find Employees Earning More Than Their Department Average

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

The inner query calculates the average salary for the department associated with the current employee. The outer query returns employees whose salaries exceed their own department's average.

This is a correlated subquery because the inner query refers to e.department_id from the outer query.

6.8 Use EXISTS to Check for Matching Records

SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

EXISTS checks whether the inner query returns at least one row. This query returns departments that have at least one employee.

6.9 Use NOT EXISTS to Find Departments Without Employees

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

NOT EXISTS returns departments for which no matching employee record exists. This is a useful pattern for finding missing relationships.

6.10 Comparison Operators Used with WHERE Subqueries

7. Using Subqueries with the FROM Clause

A subquery in the FROM clause creates a derived table. A derived table is a result set produced by an inner query that the outer query treats like a table.

Subqueries in FROM are particularly useful when you need to calculate grouped results first and then filter, summarize, or transform those results in an outer query.

7.1 Basic Syntax

SELECT column_name
FROM (
    SELECT column_name
    FROM table_name
    WHERE condition
) AS derived_table;

In MySQL, a derived table in the FROM clause must have a table alias. The alias allows the outer query to refer to the derived table and its columns.

7.2 Create a Derived Table of Employee Salaries

SELECT salary_data.employee_name,
       salary_data.salary
FROM (
    SELECT employee_name, salary
    FROM employees
) AS salary_data;

The inner query selects employee names and salaries. The outer query reads those results through the derived-table alias salary_data.

7.3 Calculate Average Salary by Department

SELECT
    department_summary.department_id,
    department_summary.average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary;

The inner query groups employees by department and calculates the average salary for each department. The outer query then retrieves the summarized values from the derived table.

7.4 Filter Departments Based on Average Salary

SELECT
    department_summary.department_id,
    department_summary.average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary
WHERE department_summary.average_salary > 60000;

The inner query calculates department-level average salaries. The outer query filters the derived table and returns only departments whose average salary is greater than 60000.

7.5 Count Employees in Each Department

SELECT
    department_counts.department_id,
    department_counts.employee_count
FROM (
    SELECT
        department_id,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
) AS department_counts
WHERE department_counts.employee_count >= 2;

The inner query counts employees in each department. The outer query returns only departments with at least two employees.

7.6 Calculate the Average of Department Average Salaries

SELECT AVG(department_summary.average_salary)
       AS average_of_department_averages
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary;

This query performs two levels of aggregation. First, the inner query calculates each department's average salary. Then, the outer query calculates the average of those department averages.

Important distinction: The average of department averages is not necessarily equal to the overall average employee salary because departments may contain different numbers of employees.

7.7 Filter Employees Using a Derived Table

SELECT
    employee_summary.employee_name,
    employee_summary.salary,
    employee_summary.salary_category
FROM (
    SELECT
        employee_name,
        salary,
        CASE
            WHEN salary >= 70000 THEN 'High'
            WHEN salary >= 50000 THEN 'Medium'
            ELSE 'Low'
        END AS salary_category
    FROM employees
) AS employee_summary
WHERE employee_summary.salary_category = 'High';

The inner query assigns each employee a salary category using CASE. The outer query filters the derived table to return employees in the High category.

7.8 Important Rules for FROM Subqueries

• Enclose the inner query in parentheses.
• Give the derived table an alias, such as department_summary.
• Give calculated columns meaningful names using aliases.
• Reference derived-table columns through the derived-table alias.
• Ensure column names are unique when the derived table exposes multiple columns.
• Use a derived table when it improves clarity or allows a second level of calculation.
• Remember that MySQL may merge or materialize a derived table depending on the query and optimization rules.

8. Comparing Subqueries in SELECT, WHERE, and FROM

9. Using SELECT, WHERE, and FROM Together

Subqueries can appear in multiple clauses of the same SQL statement. This allows you to calculate intermediate values, filter records, and display useful information in one query.

9.1 Example: Display Employees Above Their Department Average

SELECT
    e.employee_name,
    e.salary,
    e.department_id,
    (
        SELECT AVG(e2.salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id
    ) AS department_average
FROM employees e
WHERE e.salary > (
    SELECT AVG(e3.salary)
    FROM employees e3
    WHERE e3.department_id = e.department_id
);

Explanation:

1. The SELECT subquery calculates the average salary for the employee's department and displays it as department_average.
2. The WHERE subquery calculates the average salary for that same department.
3. The outer query returns employees whose salaries are greater than their department average.

This example intentionally repeats the same correlated calculation to demonstrate the use of subqueries in SELECT and WHERE. In production queries, consider whether a derived table, CTE, or another query structure would express the logic more clearly and avoid unnecessary repeated work.

9.2 Example: Summarize Departments and Display Only High-Average Departments

SELECT
    department_summary.department_id,
    department_summary.average_salary,
    (
        SELECT d.department_name
        FROM departments d
        WHERE d.department_id =
              department_summary.department_id
    ) AS department_name
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary
WHERE department_summary.average_salary > (
    SELECT AVG(salary)
    FROM employees
);

The FROM subquery calculates the average salary for each department. The WHERE subquery calculates the company-wide average and filters departments. The SELECT subquery retrieves the department name for each department summary.

This query assumes that every department ID in the derived table corresponds to one department record. In this sample database, department IDs are primary keys, so the lookup returns at most one department name.

10. Common Errors and How to Fix Them

10.1 Error: Subquery Returns More Than One Row

Problem: A scalar comparison uses a subquery that returns multiple rows.

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
);

Solution: If the inner query is expected to return multiple department IDs, use IN:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
);

10.2 Error: Missing Alias for a Derived Table

Problem: A subquery in FROM is not assigned an alias.

Solution: Give the derived table a meaningful alias.

SELECT department_summary.department_id
FROM (
    SELECT department_id
    FROM employees
    GROUP BY department_id
) AS department_summary;

10.3 Error: Unknown Column

Problem: The outer query refers to a column that does not exist in the derived table or uses an incorrect alias.

Solution: Check the inner SELECT list and use the correct table and column aliases.

10.4 Error: Duplicate or Unclear Column Names

Problem: A derived table exposes columns with duplicate or ambiguous names.

Solution: Assign clear aliases to calculated fields and select only the required columns.

10.5 Unexpected Results When Using NULL

Problem: Comparisons involving NULL can evaluate to UNKNOWN. This can affect subqueries used with IN, NOT IN, or comparison operators.

Solution: Check whether the inner query can return NULL values. Where appropriate, filter them out or use NOT EXISTS to express the intended missing-match condition.

10.6 Incorrect Grouping

Problem: A query mixes aggregate and non-aggregate columns incorrectly.

Solution: Use GROUP BY to define the groups and make sure selected non-aggregated columns are valid under the server's grouping rules.

11. Best Practices for Writing Subqueries

• Choose the right clause: Use SELECT to display calculated values, WHERE to filter records, and FROM to build intermediate result sets.
• Test the inner query first: Run the subquery independently to understand its output.
• Check the number of returned rows: Use scalar comparisons only when a single value is expected.
• Use aliases: Choose descriptive names for derived tables and calculated columns.
• Keep queries readable: Format nested queries on separate lines and indent them consistently.
• Avoid unnecessary nesting: Simplify complicated SQL when a JOIN, CTE, or derived table communicates the logic more clearly.
• Consider NULL values: Check how NULL affects comparisons and filtering.
• Use indexes where appropriate: Index columns used for joins, lookups, and correlated conditions.
• Measure performance: Use EXPLAIN to inspect the execution plan for complex queries.
• Verify results: Compare the output with a small, known dataset before using the query in an application or report.

12. Performance Considerations

The performance of a subquery depends on the query structure, data volume, available indexes, and the MySQL optimizer. It is not correct to assume that every subquery is slower than a JOIN or that every derived table creates a physical temporary table.

12.1 Use EXPLAIN

EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

EXPLAIN displays information about the planned execution of the query, including access methods, possible indexes, selected keys, and estimated rows where applicable.

12.2 Index Columns Used for Matching

For queries that match employees to departments, an index on the department ID can be useful. In the sample schema, departments.department_id is already a primary key. Consider an index on employees.department_id for workloads that frequently search or group by department.

CREATE INDEX idx_employees_department
ON employees(department_id);

Before creating an index, check existing indexes and consider the impact on storage and write operations. An index does not guarantee that every query will become faster.

12.3 Avoid Unnecessary Repeated Calculations

If the same complex calculation is repeated in multiple subqueries, consider a derived table or a Common Table Expression (CTE) to express the calculation once in the SQL structure. The optimizer's execution strategy may still vary, so inspect the plan when performance matters.

13. Practical Exercises

Use the sample employees and departments tables to complete the following exercises.

1. Use a SELECT subquery to display the average salary next to each employee.
2. Use a SELECT subquery to display the maximum salary next to each employee.
3. Use a WHERE subquery to find employees earning more than the company average.
4. Use a WHERE subquery to find employees working in the Finance department.
5. Use IN to find employees working in departments located in Mumbai or Pune.
6. Use a correlated subquery to find employees earning more than their department average.
7. Use a FROM subquery to calculate the average salary by department.
8. Use a FROM subquery to find departments with at least two employees.
9. Calculate the average of all department average salaries using a derived table.
10. Combine SELECT, WHERE, and FROM subqueries to show department averages above the company-wide average, including department names.

13.1 Sample Solution: Exercise 1

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary)
     FROM employees) AS average_salary
FROM employees;

13.2 Sample Solution: Exercise 3

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

13.3 Sample Solution: Exercise 7

SELECT
    department_summary.department_id,
    department_summary.average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary;

14. Interview Questions and Answers

Q1. What is a subquery in MySQL?

A subquery is a SQL query nested inside another query. The outer query uses its result to retrieve, filter, or calculate information.

Q2. How is a subquery used in the SELECT clause?

A subquery in SELECT can return a calculated value or related value as part of each output row, such as the company-wide average salary.

Q3. How is a subquery used in the WHERE clause?

A subquery in WHERE helps filter rows based on a value or result set returned by another query.

Q4. What is a derived table?

A derived table is the result of a subquery in the FROM clause. The outer query treats it as a table and references its columns through an alias.

Q5. Is an alias required for a derived table in MySQL?

Yes. A subquery used as a derived table in FROM must have a table alias.

Q6. What is the difference between a scalar subquery and a multiple-row subquery?

A scalar subquery returns one column and at most one row. A multiple-row subquery returns several rows and must be used in a context that supports them, such as IN or EXISTS.

Q7. Can a subquery return NULL?

Yes. A scalar subquery that returns no rows produces NULL. A subquery can also return NULL values from its selected column if the underlying data contains them.

Q8. What is a correlated subquery?

A correlated subquery refers to a column from its outer query. Its result depends on the current outer row.

Q9. Can subqueries use aggregate functions?

Yes. Aggregate functions such as AVG(), COUNT(), MIN(), MAX(), and SUM() are frequently used in subqueries.

Q10. What is the difference between a subquery in WHERE and a subquery in FROM?

A WHERE subquery is used to evaluate a filtering condition. A FROM subquery creates a derived table that the outer query can select from or filter.

Q11. Are subqueries always slower than JOINs?

No. Performance depends on the query, indexes, data distribution, and optimizer behavior. Use EXPLAIN and realistic tests to compare approaches.

Q12. How can you debug a subquery?

Run the inner query independently, inspect its returned columns and rows, verify aliases, and then integrate it into the outer query.

15. Quick Revision Checklist

• A subquery is a query nested inside another SQL statement.
• The SELECT clause can use a scalar subquery to display a calculated value.
• The WHERE clause can use a subquery to filter rows.
• The FROM clause can use a subquery to create a derived table.
• A derived table in FROM requires an alias.
• Scalar subqueries must return one column and at most one row.
• Use IN when comparing against multiple possible values.
• Correlated subqueries reference columns from the outer query.
• Aggregate functions can be used to calculate summary values.
• NULL values can affect comparison and filtering results.
• EXPLAIN helps inspect query execution plans.
• Choose the subquery structure that makes the query correct, readable, and maintainable.

16. Conclusion

Using subqueries with SELECT, WHERE, and FROM is an important MySQL skill. A SELECT subquery can display calculated information, a WHERE subquery can filter records based on another query's result, and a FROM subquery can create a derived table for further analysis.

By practicing with employee and department data, you can learn to calculate averages, identify maximum and minimum values, filter rows using related information, summarize grouped records, and combine multiple query levels into a single SQL statement.

For better results, test inner queries independently, use clear aliases, handle NULL values carefully, and check query plans when working with large datasets.

Continue Learning: Explore the MySQL Training Course for more database concepts and practical SQL exercises.

Course Demo: Register for a Course Demo to learn more about the training program.

Official Documentation: MySQL Reference Manual: Subqueries.